package ks.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import ks.po.Kid;
public class KidDAO
{
    /**
     * 根据ownerId查找t_kid表 将找到的记录封装成Kid对象通过集合返回
     * @param ownerId
     * @return
     * @throws Exception
     */
    public List<Kid> getKidsByOwnerId(int ownerId) throws Exception
    {
        List<Kid> kids = new ArrayList<Kid>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_ks","root","root");//  协议://域名(ip):端口/资源（数据库名）
            ps = con.prepareStatement("select * from t_kid where ownerId=?");
            ps.setInt(1, ownerId);
            rs = ps.executeQuery();
            while(rs.next())
            {
                Kid kid = new Kid();
                kid.setBirthdate(rs.getString("birthdate"));
                kid.setId(rs.getInt("id"));
                kid.setName(rs.getString("name"));
                kid.setOwnerId(ownerId);
                kid.setPhoto(rs.getString("ksoto"));
                kids.add(kid);
            }
        }
        catch(ClassNotFoundException e)
        {
            e.printStackTrace();
            throw new Exception("找不到驱动:"+e.getMessage());//异常不能在底层丢失了
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw new Exception("数据库操作错误:"+e.getMessage());
        }
        finally
        {
            if(rs!=null)
            {
                rs.close();
            }
            if(ps!=null)
            {
                ps.close();
            }
            if(con!=null)
            {
                con.close();
            }
        }
        return kids;
    }

    /**
     * author：hlzhang 20180130
     * 根据幼儿名称和主人姓名，查找幼儿，并封装为幼儿实体类的对象集合
     * @param kidName 幼儿名称
     * @param ownerName 主人姓名
     * @return 幼儿集合
     * @throws Exception
     */
    public List<Kid> search(String kidName, String ownerName) throws Exception
    {
        List<Kid> kids = new ArrayList<Kid>();
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_ks", "root", "root");
            // 1.找符合条件的老师
            String sql = "select t_kid.*, t_user.name from t_kid, t_user  where t_kid.ownerId=t_user.id and t_kid.name like ? and t_user.name like ?";
            ps = con.prepareStatement(sql);
            ps.setString(1, "%" + kidName + "%");
            ps.setString(2, "%" + ownerName + "%");
            rs = ps.executeQuery();
            while (rs.next())
            {
                Kid kid = new Kid();// 这里查询的是t_kid.* 所以使用实体类Kid封装
                kid.setId(rs.getInt("id"));
                kid.setName(rs.getString("name"));
                kid.setBirthdate(rs.getString("birthdate"));
                kid.setPhoto(rs.getString("ksoto"));
                kid.setOwnerId(rs.getInt("ownerId"));
                kid.setOwnerName(rs.getString("t_user.name"));
                kids.add(kid);
            }
        }
        catch (ClassNotFoundException e)
        {
            e.printStackTrace();
            throw new Exception("找不到驱动:" + e.getMessage());
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw new Exception("SQL异常:" + e.getMessage());
        }
        finally
        {
            if (rs != null)
            {
                rs.close();
            }
            if (ps != null)
            {
                ps.close();
            }
            if (con != null)
            {
                con.close();
            }
        }
        return kids;
    }


    /**
     * 根据kidId 删除t_kid表的一行记录
     * @param kidId
     * @return void
     * @throws Exception
     */
    public void delete(int kidId) throws Exception
    {
        Connection con = null;
        PreparedStatement ps = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_ks","root","root");//  协议://域名(ip):端口/资源（数据库名）
            ps = con.prepareStatement("delete from t_kid where id=?");
            ps.setInt(1, kidId);
            ps.executeUpdate();
        }
        catch(ClassNotFoundException e)
        {
            e.printStackTrace();
            throw new Exception("找不到驱动:"+e.getMessage());//异常不能在底层丢失了
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            throw new Exception("数据库操作错误:"+e.getMessage());
        }
        finally
        {
            if(ps!=null)
            {
                ps.close();
            }
            if(con!=null)
            {
                con.close();
            }
        }
    }

    /**
     * 将kid对象的name  birthdate  ksoto  ownerid的值保存成为t_kid表中的一条新纪录
     * @param kid
     * @throws Exception
     */
    public void save(Kid kid) throws Exception
    {
        Connection con = null;
        PreparedStatement ps = null;
        try
        {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://localhost:3306/db_ks","root","root");//  协议://域名(ip):端口/资源（数据库名）
            ps = con.prepareStatement("insert into t_kid value(null,?,?,?,?)");
            ps.setString(1, kid.getName());
            ps.setString(2, kid.getBirthdate());
            ps.setString(3, kid.getPhoto());
            ps.setInt(4, kid.getOwnerId());
            ps.executeUpdate();
        }
        catch(ClassNotFoundException e)
        {
            e.printStackTrace();
            throw new Exception("找不到驱动:"+e.getMessage());//异常不能在底层丢失了
        }catch (SQLException e)
        {
            e.printStackTrace();
            throw new Exception("数据库操作错误:"+e.getMessage());
        }
        finally
        {
            if(ps!=null)
            {
                ps.close();
            }
            if(con!=null)
            {
                con.close();
            }
        }
    }
}
